package com.tensquare.qa.dao;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

import com.tensquare.qa.po.Problem;
import org.springframework.data.jpa.repository.Query;

/**
 * 数据访问接口
 *
 * @author Administrator
 */
public interface ProblemRepository extends JpaRepository<Problem, String>, JpaSpecificationExecutor<Problem> {
    /**
     * 根据base数据库label表id为1的数据
     * 按照最新的回复时间进行排序
     * 获取分页对象
     */
    @Query(value = "SELECT * FROM tb_problem, tb_pl WHERE  problemid = id  AND labelid = ?1 ORDER BY replytime DESC", nativeQuery = true)
    public Page<Problem> findByLabelidOrderByReplytimeDesc(String labelId, Pageable pageable);


    /**
     * 根据标签id[Java, Python, PHP...]查询热门回复的分页列表
     */
    @Query(value = "SELECT * FROM tb_problem, tb_pl WHERE  problemid = id  AND labelid = ?1  ORDER BY reply DESC", nativeQuery = true)
    public Page<Problem> findListByLabelIdOrderByReplyDesc(String labelId, Pageable pageable);

    /**
     * 根据标签id[Java, Python, PHP...]查询未回复的分页列表
     */
    @Query(value = "SELECT * FROM tb_problem, tb_pl WHERE  problemid = id  AND labelid = ?1  AND reply = 0 ORDER BY createtime DESC", nativeQuery = true)
    public Page<Problem> findListByLabelIdAndReplyNotOrderByCreatetimeDesc(String labelId, Pageable pageable);
}

